#!/usr/bin/env python
# -*- coding: utf-8 -*-
import web
import json
import datetime
import openpyxl
from openpyxl.styles import Font, colors, Alignment, Border, Side, PatternFill
from openpyxl.comments import Comment
from openpyxl.worksheet.datavalidation import DataValidation
from db import db

urls = (
    "/excel/test", "Excel",
)
sql = db.mysql()
app = web.application(urls, globals())


class Result(object):
    def __init__(self, code=0, msg="成功", data=None):
        self.code = code
        self.msg = msg
        self.data = data


class Excel:
    def GET(self):
        wb = openpyxl.Workbook()
        # 创建工作表
        sheet = wb.create_sheet('工作表1', 0)

        # 生成数据
        sheet.cell(row=1, column=1).value = "1,1"
        sheet['A2'] = 'A2'
        sheet.append([1, datetime.datetime.now().strftime("%Y-%m-%d"), False, '530128199512133314'])
        # 显示全部工作表
        print(wb.sheetnames)
        # 选择工作表
        sheet2 = wb.get_sheet_by_name('Sheet')
        sheet2.sheet_properties.tabColor = '1072BA'
        cell = sheet2['A1']

        # 设置字体样式
        cell.font = Font(name='黑体', size=16, italic=False, color=colors.RED, bold=True)
        cell.value = '我是黑体16号、加粗、红色字体'
        # 设置对其方式
        cell2 = sheet2['A2']
        cell2.alignment = Alignment(horizontal='right', vertical='center')
        cell2.value = '文字水平靠右，垂直居中'
        # 设置边框样式和填充样式
        cell3 = sheet2['A3']
        side = Side(border_style='dashDotDot', color=colors.BLUE)
        # 上、有、下、左、对角线、对角线方向、垂直、水平
        border = Border(top=side, right=side, bottom=side, left=side,
                        diagonal=side, diagonal_direction=side, outline=side,
                        vertical=side, horizontal=side)
        cell3.border = border

        # 单元格填充
        cell3.fill = PatternFill(fill_type='lightVertical', start_color=colors.BLUE, end_color=colors.DARKBLUE)
        # 设置行高和列宽
        sheet2.row_dimensions[2].height = 50
        sheet2.column_dimensions['A'].width = 100
        # 合并和拆分单元格
        sheet2.merge_cells('A1:D1')
        sheet2.merge_cells('A2:D2')
        sheet2.unmerge_cells('A1:D1')

        # 添加批注
        comment = Comment('合并单元格A2:D2', 'ps李兴武')
        comment.width = 300
        comment.height = 50
        sheet2['A2'].comment = comment

        # 筛选和排序【这会将相关指令添加到文件中，但实际上不会过滤或排序】
        # 1.准备数据
        sheet3 = wb.create_sheet('数据3', 2)
        data = [
            ["Fruit", "Quantity"],
            ["Kiwi", 3],
            ["Grape", 15],
            ["Apple", 3],
            ["Peach", 3],
            ["Pomegranate", 3],
            ["Pear", 3],
            ["Tangerine", 2],
            ["Blueberry", 2],
            ["Mango", 3],
            ["Watermelon", 3],
            ["Blackberry", 8],
            ["Orange", 3],
            ["Raspberry", 7],
            ["Banana", 3]
        ]
        for r in data:
            sheet3.append(r)
        # 2.指定筛选的区间
        sheet3.auto_filter.ref = "A1:B15"
        # 3.选择筛选条件，列号、条件
        # sheet3.auto_filter.add_filter_column(0, ["Kiwi", "Apple", "Mango"])
        sheet3.auto_filter.add_filter_column(1, [2, 7])
        # 4.指定排序条件
        sheet3.auto_filter.add_sort_condition("B2:B15")

        # 数据验证
        # 1.准备数据
        sheet4 = wb.create_sheet('数据4', 3)
        data2 = [
            ["姓名", "性别", "年龄"],
            ["Kiwi", '男', 29],
            ["Grape", '女', 15],
            ["Apple", '男', 3],
            ["Peach", '女', 3],
            ["Pomegranate", '男', 3],
            ["Pear", '女', 3],
            ["Tangerine", '秀吉', 2],
            ["Blueberry", '女', 2],
            ["Mango", '男', 3],
            ["Watermelon", '女', 3],
            ["Blackberry", '秀吉', 8],
            ["Orange", '女', 3],
            ["Raspberry", '男', 7],
            ["Banana", '其他', 3]
        ]
        for r in data2:
            sheet4.append(r)
        # 2.创建数据验证对象，数据类型、条件、是否允许空
        dv = DataValidation(type='list', formula1='"男,女"', allow_blank=True)
        # 3.自定义提示标题和内容、错误的标题和提示内容
        dv.prompt = '请从列表中选择'
        dv.promptTitle = '选择'
        dv.error = '数据非法输入,请从列表中选择.'
        dv.errorTitle = '错误的选择'
        # 4.定义校验的区间
        dv.add('B2:B15')
        # 5.添加到工作表中
        sheet4.add_data_validation(dv)

        # 打印设置
        # 打印水平、垂直居中
        sheet4.print_options.horizontalCentered = True
        sheet4.print_options.verticalCentered = True
        # 设置页眉页脚
        sheet4.oddHeader.right.text = '喵帕斯科技'
        sheet4.oddHeader.right.size = 20
        sheet4.oddHeader.right.font = '微软雅黑,Bold'
        sheet4.oddHeader.right.color = "CC3366"
        sheet4.oddFooter.center.text = '第&[Page]页，共&N页'
        sheet4.oddFooter.center.size = '12'
        # 设置打印区间
        sheet4.print_area = 'A1:C15'
        # 设置打印标题
        sheet4.print_title_cols = 'A:C'
        sheet4.print_title_rows = '1:15'
        # 保存内容
        wb.save('temp/工作簿.xlsx')
        web.header('content-type', 'text/json')
        return json.dumps(Result(data='/path/1254').__dict__)


if __name__ == "__main__":
    app.run()
